
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Reflection;
namespace THSLib
{
    public class THSDbUtil : BaseBusinessController
    {
        private static readonly string spGetAllowNullTableColumn = "GEDbUtil_SelectAllowNullTableColumn";
        private static readonly string spGetAllStoredProcedures = "GEDbUtil_SelectAllStoredProcedures";
        private static readonly string spGetAllTables = "GEDbUtil_SelectAllTables";
        private static readonly string spGetAllViews = "GEDbUtil_SelectAllViews";
        private static readonly string spGetColumnDescriptionByTableNameAndColumnName = "GEDbUtil_SelectColumnDescriptionByTableNameAndColumnName";
        private static readonly string spGetColumnsByTableNameAndColumnNameBeginWith = "GEDbUtil_SelectColumnsByTableNameAndColumnNameBeginWith";
        private static readonly string spGetForeignColumnByPrimaryTableAndForeignTable = "GEDbUtil_SelectForeignColumnByPrimaryTableAndForeignTable";
        private static readonly string spGetMCodeColumnsFromTable = "GEDbUtil_SelectMCodeColumnsFromTable";
        private static readonly string spGetNotAllowNullTableColumns = "GEDbUtil_SelectNotAllowNullTableColumns";
        private static readonly string spGetPrimaryTableAndColumnWhichForeignColumnReferenceTo = "GEDbUtil_SelectPrimaryTableAndColumnWhichForeignColumnReferenceTo";
        private static readonly string spGetSaveStatusInSessionColumn = "GEDbUtil_SelectSaveStatusInSessionColumn";
        private static readonly string spGetStoredProcedureByName = "GEDbUtil_SelectStoredProcedureByName";
        private static readonly string spGetStoredProcedureByTableName = "GEDbUtil_SelectStoredProcedureByTableName";
        private static readonly string spGetStoredProcedureTextByID = "GEDbUtil_SelectStoredProcedureTextByID";
        private static readonly string spGetStoredProcedureTextByStoredProcedureName = "GEDbUtil_SelectStoredProcedureTextByStoredProcedureName";
        private static readonly string spGetTableByName = "GEDbUtil_SelectTableByName";
        private static readonly string spGetTableColumn = "GEDbUtil_SelectTableColumn";
        private static readonly string spGetTableColumns = "GEDbUtil_SelectTableColumns";
        private static readonly string spGetTableForeignKey = "GEDbUtil_SelectTableForeignKey";
        private static readonly string spGetTableForeignKeys = "GEDbUtil_SelectTableForeignKeys";
        private static readonly string spGetTableHaveMCode = "GEDbUtil_SelectTableHaveMCode";
        private static readonly string spGetTableKeyColumns = "GEDbUtil_SelectTableKeyColumns";
        private static readonly string spGetTablePrimaryKey = "GEDbUtil_SelectTablePrimaryKey";
        private static readonly string spGetTablePrimaryKeys = "GEDbUtil_SelectTablePrimaryKeys";
        private static readonly string spGetTableUniqueContraint = "GEDbUtil_SelectTableUniqueConstraint";
        private static readonly string spGetViewColumn = "GEDbUtil_SelectViewColumn";
        private static readonly string spGetViewColumns = "GEDbUtil_SelectViewColumns";
        private static readonly string spGetViewTextByViewName = "GEDbUtil_SelectViewTextByViewName";
        private static readonly string spGetAllTablesReference = "GEDBUtil_SelectAllTablesReference";
		
        public THSDbUtil()
        {
            base.dal = new DALBaseProvider();
        }
       

        public static bool ColumnIsExist(string strTableName, string strColumnName)
        {
            DataSet tableColumn = GetTableColumn(strTableName, strColumnName);
            return ((tableColumn.Tables.Count > 0) && (tableColumn.Tables[0].Rows.Count > 0));
        }

        public static bool ColumnIsExistInBaseBusinessObject(string strPropertyName)
        {
            PropertyInfo[] properties = typeof(BusinessObject).GetProperties();
            foreach (PropertyInfo info in properties)
            {
                if (info.Name.Equals(strPropertyName))
                {
                    return true;
                }
            }
            return false;
        }
        public String GetSaveStatusInSessionColumn(String strTableName)
        {
            String strQuery = String.Format("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}' AND COLUMN_NAME LIKE '%SaveStatusInSession'", strTableName);
            DataSet ds = dal.GetDataSet(strQuery);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return ds.Tables[0].Rows[0]["COLUMN_NAME"].ToString();
                }
            }
            return String.Empty;
        }
        public static void CreateColumnDescription(string strTableName, string strColumnName, string strColumnDescription)
        {
            SqlDbHelper.RunStoredProcedure("sp_addextendedproperty", new object[] { "MS_Description", strColumnDescription, "Schema", "dbo", "Table", strTableName, "Column", strColumnName });
        }

        public static DataSet ExecuteQuery(string strQuery)
        {
            return SqlDbHelper.RunQuery(SqlDbHelper.GetQuery(strQuery));
        }

        public static string ExecuteStoredProcedureScript(string strStoredProcedureScript)
        {
            return SqlDbHelper.ExecuteStoredProcedureScript(strStoredProcedureScript);
        }

        public static DataSet GetAllowNullTableColumn(string strTableName, string colName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetAllowNullTableColumn, new object[] { strTableName, colName });
        }

        public static DataSet GetAllStoredProcedures()
        {
            return SqlDbHelper.RunStoredProcedure(spGetAllStoredProcedures);
        }

        public static DataSet GetAllTables()
        {
            return SqlDbHelper.RunStoredProcedure(spGetAllTables);
        }

        public static DataSet GetAllViews()
        {
            return SqlDbHelper.RunStoredProcedure(spGetAllViews);
        }
        public static DataSet GetAllTablesReference(String strTableName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetAllTablesReference,strTableName);
        }
        public static int GetColumnCharacterMaximumLength(string strTableName, string strColumnName)
        {
            int num = 0;
            DataSet tableColumn = GetTableColumn(strTableName, strColumnName);
            if ((tableColumn.Tables.Count > 0) && (tableColumn.Tables[0].Rows.Count > 0))
            {
                switch (tableColumn.Tables[0].Rows[0]["DATA_TYPE"].ToString())
                {
                    case "varchar":
                    case "nvarchar":
                        num = Convert.ToInt32(tableColumn.Tables[0].Rows[0]["CHARACTER_MAXIMUM_LENGTH"]);
                        break;
                }
            }
            tableColumn.Dispose();
            return num;
        }

        public static string GetColumnDataType(string strTableName, string strColumnName)
        {
            string str = string.Empty;
            DataSet tableColumn = GetTableColumn(strTableName, strColumnName);
            if ((tableColumn.Tables.Count > 0) && (tableColumn.Tables[0].Rows.Count > 0))
            {
                str = tableColumn.Tables[0].Rows[0]["DATA_TYPE"].ToString();
            }
            return str;
        }

        public static string GetColumnDbType(string strTableName, string strColumnName)
        {
            string str = string.Empty;
            DataSet tableColumn = GetTableColumn(strTableName, strColumnName);
            if (tableColumn.Tables.Count > 0)
            {
                if (tableColumn.Tables[0].Rows.Count <= 0)
                {
                    return str;
                }
                str = tableColumn.Tables[0].Rows[0]["DATA_TYPE"].ToString();
                switch (str)
                {
                    case "varchar":
                    case "nvarchar":
                    case "varbinary":
                        str = str + "(" + tableColumn.Tables[0].Rows[0]["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")";
                        break;
                }
            }
            return str;
        }

        public static string GetColumnDescriptionFromTableNameAndColumnName(string strTableName, string strColumName)
        {
            string str = string.Empty;
            DataSet set = SqlDbHelper.RunStoredProcedure(spGetColumnDescriptionByTableNameAndColumnName, new object[] { strTableName, strColumName });
            if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0))
            {
                str = set.Tables[0].Rows[0]["value"].ToString();
            }
            return str;
        }

        public static Type GetCSharpVariableType(string strTableName, string strColumnName)
        {
            switch (GetColumnDataType(strTableName, strColumnName))
            {
                case "varchar":
                    return typeof(string);

                case "nvarchar":
                    return typeof(string);

                case "text":
                    return typeof(string);

                case "ntext":
                    return typeof(string);

                case "int":
                    return typeof(int);

                case "float":
                    return typeof(double);

                case "real":
                    return typeof(double);

                case "decimal":
                    return typeof(decimal);

                case "datetime":
                    return typeof(DateTime);

                case "bit":
                    return typeof(bool);

                case "image":
                    return typeof(byte[]);

                case "varbinary":
                    return typeof(byte[]);
            }
            return typeof(string);
        }

        public static DataSet GetDataFromTableNameWithTableColumnLikeColumnValue(string strTableName, string strColumnName, string strColumnValue)
        {
            return SqlDbHelper.RunQuery(SqlDbHelper.GetQuery(string.Format("SELECT * FROM [dbo].[{0}] WHERE [{1}] LIKE '%{2}%' ORDER BY [{1}]", strTableName, strColumnName, strColumnValue)));
        }
        public static String GetPropertyStringValue(object obj, String strPropertyName)
        {
            object objValue = GetPropertyValue(obj, strPropertyName);
            if (objValue != null)
                return objValue.ToString();
            return String.Empty;
        }
        public static bool ColumnIsAllowNull(String strTableName, String strColumnName)
        {
            DataSet ds = GetTableColumn(strTableName, strColumnName);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables[0].Rows[0]["IS_NULLABLE"].ToString() == "YES")
                        return true;
                    else
                        return false;
                }

            }
            return true;
        }


        public static string GetForeignColumn(string strPrimaryTable, string strForeignTable)
        {
            string str = string.Empty;
            DataSet set = SqlDbHelper.RunStoredProcedure(spGetForeignColumnByPrimaryTableAndForeignTable, new object[] { strPrimaryTable, strForeignTable });
            if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0))
            {
                str = set.Tables[0].Rows[0][0].ToString();
            }
            return str;
        }

        public static DateTime GetLastCreatedDateOfTable(string strTableName)
        {
            try
            {
                DateTime minValue = DateTime.MinValue;
                string strColumnName = "AACreatedDate";
                if (ColumnIsExist(strTableName, strColumnName))
                {
                    DataSet set = SqlDbHelper.RunQuery(SqlDbHelper.GetQuery(string.Format("Select MAX([{0}]) From [{1}] WHERE [{2}]<'99991231 23:59:59:000' ", strColumnName, strTableName, strColumnName)));
                    if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0))
                    {
                        minValue = Convert.ToDateTime(set.Tables[0].Rows[0][0]);
                    }
                }
                return minValue;
            }
            catch (Exception)
            {
                return DateTime.MinValue;
            }
        }

        public static DateTime GetLastUpdatedDateOfTable(string strTableName)
        {
            try
            {
                DateTime minValue = DateTime.MinValue;
                string strColumnName = "AAUpdatedDate";
                if (ColumnIsExist(strTableName, strColumnName))
                {
                    DataSet set = SqlDbHelper.RunQuery(SqlDbHelper.GetQuery(string.Format("Select MAX([{0}]) From [{1}] WHERE [{2}]<'99991231 23:59:59:000' ", strColumnName, strTableName, strColumnName)));
                    if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0))
                    {
                        minValue = Convert.ToDateTime(set.Tables[0].Rows[0][0]);
                    }
                }
                return minValue;
            }
            catch (Exception)
            {
                return DateTime.MinValue;
            }
        }

        public static List<STModuleTablesInfo> GetListModuleObjectByModuleID(int iSTModuleID)
        {
            STModuleTablesController controller = new STModuleTablesController();
            DataSet tablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex = controller.GetTablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex(iSTModuleID, 1);
            if ((tablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex != null) && (tablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex.Tables[0].Rows.Count > 0))
            {
                List<STModuleTablesInfo> list = new List<STModuleTablesInfo>();
                foreach (DataRow row in tablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex.Tables[0].Rows)
                {
                    STModuleTablesInfo objectFromDataRow = (STModuleTablesInfo)controller.GetObjectFromDataRow(row);
                    if (objectFromDataRow != null)
                    {
                        list.Add(objectFromDataRow);
                    }
                }
                return list;
            }
            return null;
        }

        public static STModuleTablesInfo GetMainObjectByModuleID(int iSTModuleID)
        {
            STModuleTablesController controller = new STModuleTablesController();
            DataSet tablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex = controller.GetTablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex(iSTModuleID, 0);
            if ((tablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex != null) && (tablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex.Tables[0].Rows.Count > 0))
            {
                STModuleTablesInfo objectFromDataRow = (STModuleTablesInfo)controller.GetObjectFromDataRow(tablesOfSTModuleTablesBySTModuleIDAndSTModuleTableLevelIndex.Tables[0].Rows[0]);
                if (objectFromDataRow != null)
                {
                    return objectFromDataRow;
                }
            }
            return null;
        }

        public static DataSet GetMCodeColumnsFromTable(string strTableName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetMCodeColumnsFromTable, new object[] { strTableName });
        }

        public static string GetNameColumnOfTable(string strTableName)
        {
            string tablePrimaryColumn = GetTablePrimaryColumn(strTableName);
            if (!string.IsNullOrEmpty(tablePrimaryColumn))
            {
                return (tablePrimaryColumn.Substring(0, tablePrimaryColumn.Length - 2) + "Name");
            }
            return string.Empty;
        }

        public static DataSet GetNotAllowNullTableColumns(string strTableName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetNotAllowNullTableColumns, new object[] { strTableName });
        }

        public static string GetPrimaryColumnWhichForeignColumnReferenceTo(string strForeignTableName, string strForeignColumnName)
        {
            string str = string.Empty;
            DataSet set = SqlDbHelper.RunStoredProcedure(spGetPrimaryTableAndColumnWhichForeignColumnReferenceTo, new object[] { strForeignTableName, strForeignColumnName });
            if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0))
            {
                str = set.Tables[0].Rows[0][1].ToString();
            }
            return str;
        }

        public static string GetPrimaryTableWhichForeignColumnReferenceTo(string strForeignTableName, string strForeignColumnName)
        {
            string str = string.Empty;
            DataSet set = SqlDbHelper.RunStoredProcedure(spGetPrimaryTableAndColumnWhichForeignColumnReferenceTo, new object[] { strForeignTableName, strForeignColumnName });
            if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0))
            {
                str = set.Tables[0].Rows[0][0].ToString();
            }
            return str;
        }

        public static object GetPropertyValue(object obj, string strPropertyName)
        {
            PropertyInfo property = obj.GetType().GetProperty(strPropertyName);
            if (property != null)
            {
               return DynamicInvoker.DynamicGetValue(obj, property);
            }
            return null;
        }

        public static object GetPropertyValue(BusinessObject obj, string strPropertyName)
        {
            PropertyInfo property = obj.GetType().GetProperty(strPropertyName);
            if (property != null)
            {
                return DynamicInvoker.DynamicGetValue(obj, property);
            }
            return null;
        }

        public static DataSet GetStoredProcedureByTableName(string strTableName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetStoredProcedureByTableName, new object[] { strTableName });
        }

        public static string GetStoredProcedureTextByID(int id)
        {
            string str = string.Empty;
            DataSet set = SqlDbHelper.RunStoredProcedure(spGetStoredProcedureTextByID, new object[] { id });
            if (set.Tables.Count > 0)
            {
                foreach (DataRow row in set.Tables[0].Rows)
                {
                    str = str + row["text"].ToString();
                }
            }
            return str;
        }

        public static string GetStoredProcedureTextByStoredProcedureName(string strStoredProcedureName)
        {
            string str = string.Empty;
            DataSet set = SqlDbHelper.RunStoredProcedure(spGetStoredProcedureTextByStoredProcedureName, new object[] { strStoredProcedureName });
            if (set.Tables.Count > 0)
            {
                foreach (DataRow row in set.Tables[0].Rows)
                {
                    str = str + row["text"].ToString();
                }
            }
            return str;
        }

        public static DataSet GetTableColumn(string strTableName, string strColumnName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetTableColumn, new object[] { strTableName, strColumnName });
        }

        public static int GetTableColumnCount(string strTableName)
        {
            try
            {
                return GetTableColumns(strTableName).Tables[0].Rows.Count;
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public static DataSet GetTableColumns(string strTableName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetTableColumns, new object[] { strTableName });
        }

        public static DataSet GetTableColumnsByTableNameAndColumnBeginWith(string strTableName, string strColumnName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetColumnsByTableNameAndColumnNameBeginWith, new object[] { strTableName, strColumnName });
        }

        public static DataSet GetTableForeignKey(string strTableName, string strColumnName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetTableForeignKey, new object[] { strTableName, strColumnName });
        }

        public static DataSet GetTableForeignKeys(string strTableName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetTableForeignKeys, new object[] { strTableName });
        }

        public static DataSet GetTableKeyColumns(string strTableName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetTableKeyColumns, new object[] { strTableName });
        }

        public static string GetTableNameByViewNameAndColumnName(string strViewName, string strColumnName)
        {
            DataSet viewColumn = GetViewColumn(strViewName, strColumnName);
            if ((viewColumn.Tables.Count > 0) && (viewColumn.Tables[0].Rows.Count > 0))
            {
                return viewColumn.Tables[0].Rows[0]["TABLE_NAME"].ToString();
            }
            return string.Empty;
        }

        public static string GetTablePrimaryColumn(string strTableName)
        {
            return SqlDbHelper.PrimaryColumnList[strTableName].ToString();
        }

        public static DataSet GetTablePrimaryKey(string strTableName, string strColumnName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetTablePrimaryKey, new object[] { strTableName, strColumnName });
        }

        public static DataSet GetTablePrimaryKeys(string strTableName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetTablePrimaryKeys, new object[] { strTableName });
        }

        public static DataSet GetTablesHaveMCode()
        {
            return SqlDbHelper.RunStoredProcedure(spGetTableHaveMCode);
        }

        public static DataSet GetTableUniqueConstraint(string strTableName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetTableUniqueContraint, new object[] { strTableName });
        }

        public static DataSet GetViewColumn(string strViewName, string strColumnName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetViewColumn, new object[] { strViewName, strColumnName });
        }

        public static DataSet GetViewColumns(string strViewName)
        {
            return SqlDbHelper.RunStoredProcedure(spGetViewColumns, new object[] { strViewName });
        }

        public static string GetViewTextByViewName(string strViewName)
        {
            string str = string.Empty;
            DataSet set = SqlDbHelper.RunStoredProcedure(spGetViewTextByViewName, new object[] { strViewName });
            if (set.Tables.Count > 0)
            {
                foreach (DataRow row in set.Tables[0].Rows)
                {
                    str = str + row["text"].ToString();
                }
            }
            return str;
        }

        public static bool IsExistReport(string strReportName)
        {
            bool flag = false;
            STRptsInfo objectByName = (STRptsInfo)new STRptsController().GetObjectByName(strReportName);
            if (objectByName != null)
            {
                flag = true;
            }
            return flag;
        }

        public static bool IsExistTable(string strTableName)
        {
            bool flag = false;
            DataSet set = SqlDbHelper.RunStoredProcedure(spGetTableByName, new object[] { strTableName });
            if ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0))
            {
                flag = true;
            }
            return flag;
        }

        public static bool IsForeignKey(string strTableName, string strColumnName)
        {
            return strColumnName.StartsWith("FK_");
        }

        public static bool IsPrimaryKey(string strTableName, string strColumnName)
        {
            bool flag = false;
            DataSet tablePrimaryKey = GetTablePrimaryKey(strTableName, strColumnName);
            if ((tablePrimaryKey.Tables.Count > 0) && (tablePrimaryKey.Tables[0].Rows.Count > 0))
            {
                flag = true;
            }
            return flag;
        }

        public static void SetPropertyValue(object obj, string strPropertyName, object value)
        {
            PropertyInfo property = obj.GetType().GetProperty(strPropertyName);
            if (property != null)
            {
                DynamicInvoker.DynamicSetValue(obj, property, value);
            }
        }

        public static void SetPropertyValue(BusinessObject obj, string strPropertyName, object value)
        {
            PropertyInfo property = obj.GetType().GetProperty(strPropertyName);
            if (property != null)
            {
                DynamicInvoker.DynamicSetValue(obj, property, value);
            }
        }

        public static bool StoredProcedureIsExist(string strStoredProcedureName)
        {
            DataSet set = SqlDbHelper.RunStoredProcedure(spGetStoredProcedureByName, new object[] { strStoredProcedureName });
            return ((set.Tables.Count > 0) && (set.Tables[0].Rows.Count > 0));
        }

        public static void UpdateColumnDescription(string strTableName, string strColumnName, string strColumnDescription)
        {
            SqlDbHelper.RunStoredProcedure("sp_updateextendedproperty", new object[] { "MS_Description", strColumnDescription, "Schema", "dbo", "Table", strTableName, "Column", strColumnName });
        }
		   public static String SynchronizeData(String publicationName, String subscriberName, Delegate handler)
        {
            return SqlDbHelper.SynchronizeData(publicationName, subscriberName, handler);
        }
    }
}

